package com.ctshk.rpc.bus.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.ctshk.common.req.MainData;
import com.ctshk.common.service.mapper.CtsBaseMapper;
import com.ctshk.rpc.bus.dto.BusLineListDTO;
import com.ctshk.rpc.bus.entity.BusLine;
import com.ctshk.rpc.bus.req.BusArriveCityListByLineQueryReq;
import com.ctshk.rpc.bus.req.BusDepartureCityListByLineQueryReq;
import com.ctshk.rpc.bus.req.BusLinePageReq;
import com.ctshk.rpc.bus.req.BusLineQueryReq;
import com.ctshk.rpc.bus.req.BusStopsListByLineQueryReq;

/**
 * <p>
 * 巴士线路管理 Mapper 接口
 * </p>
 *
 * @author 彭柳霖
 * @since 2021-02-08
 */
public interface BusLineMapper extends CtsBaseMapper<BusLine> {

    /**
     * 获取巴士路线列表
     * @param req
     * @return
     */
    @Select("<script>"+"SELECT t1.id,t1.bus_companies_id,t1.bus_companies_name, \n" +
            "t1.line_name,t1.vehicle_type,GROUP_CONCAT(distinct t2.seat_number) as seatNumbers, \n" +
            "t1.departure_city_name,t1.arrive_city_name,GROUP_CONCAT(distinct t4.site_name order by t4.id) as siteNames, \n" +
            "t1.gateway_name,t1.estimated_time,t1.status, \n" +
            "t1.child_age_begin,t1.child_age_end, \n" +
            "t1.adult_age_begin,t1.adult_age_end,t1.old_age_begin \n" +
            "FROM t_bus_line t1 \n" +
            "left join t_bus_line_seat t2 on t1.id = t2.line_id \n" +
            "left join t_bus_line_stops t3 on t1.id = t3.line_id \n" +
            "left join t_bus_stops t4 on t3.stops_id = t4.id \n" +
            "WHERE t1.is_deleted = 0 <if test=\"req.busCompaniesName != null and req.busCompaniesName != ''\">AND t1.bus_companies_name LIKE CONCAT('%',#{req.busCompaniesName},'%')</if> \n" +
            "<if test=\"req.lineName != null and req.lineName != ''\">AND t1.line_name LIKE CONCAT('%',#{req.lineName},'%')</if> \n" +
            "<if test=\"req.siteName != null and req.siteName != ''\">AND t4.site_name LIKE CONCAT('%',#{req.siteName},'%')</if> \n" +
            "<if test=\"req.status != null and req.status != 0\">AND t1.status = #{req.status}</if> \n" +
            "GROUP BY t1.id order by t1.gmt_create desc"+"</script>")
    IPage<BusLineListDTO> queryList(@Param("req") BusLinePageReq req, IPage<BusLineListDTO> agentPage);

    /**
     * 查询巴士线路的出发城市列表
     */
    @Select("select distinct t.departure_city_id as value, t.departure_city_name as label from t_bus_line t where t.is_deleted = 0 and t.bus_companies_id = #{busCompaniesId}")
    List<MainData> queryDepartureCityListByLine(BusDepartureCityListByLineQueryReq req);
    
    /**
     * 查询巴士线路的到达城市列表
     */
    @Select("select distinct t.arrive_city_id as value, t.arrive_city_name as label from t_bus_line t where t.is_deleted = 0 and t.bus_companies_id = #{busCompaniesId} and t.departure_city_id = #{departureCityId}")
    List<MainData> queryArriveCityListByLine(BusArriveCityListByLineQueryReq req);
    
    /**
     * 查询巴士线路的站点列表
     */
    @Select("select distinct t.id AS value, t.site_name AS label from t_bus_stops t"
    		+ " join t_bus_line_stops tbls on tbls.stops_id = t.id "
    		+ " join t_bus_line tbl on tbl.id = tbls.line_id "
    		+ " where t.is_deleted = 0 and tbl.is_deleted = 0 and tbl.bus_companies_id = #{busCompaniesId} and tbl.departure_city_id = #{departureCityId} and tbl.arrive_city_id = #{arriveCityId} ")
    List<MainData> queryStopsListByLine(BusStopsListByLineQueryReq req);
    
    /**
     * 查询巴士线路的上车站列表
     */
    @Select("select distinct t.departure_station_id AS value, t.departure_station_name AS label from t_bus_line t where t.is_deleted = 0 and t.bus_companies_id = #{busCompaniesId} and t.departure_city_id = #{departureCityId} and t.arrive_city_id = #{arriveCityId}")
    List<MainData> queryDepartureStopsListByLine(BusStopsListByLineQueryReq req);
    
    /**
     * 查询巴士线路的下车站列表
     */
    @Select("select distinct t.terminus_id AS value, t.terminus_name AS label from t_bus_line t where t.is_deleted = 0 and t.bus_companies_id = #{busCompaniesId} and t.departure_city_id = #{departureCityId} and t.arrive_city_id = #{arriveCityId}")
    List<MainData> queryTerminusStopsListByLine(BusStopsListByLineQueryReq req);
    
    /**
     * 查询巴士列表
     * @param req
     * @return
     */
    default List<BusLine> queryList(BusLineQueryReq req) {
    	return selectList(newCtsWrapper()
        		.ifNotNullEq("bus_companies_id", req.getBusCompaniesId())
        		.ifNotNullEq("departure_city_id", req.getDepartureCityId())
        		.ifNotNullEq("arrive_city_id", req.getArriveCityId())
        		.ifNotBlankLike("bus_companies_name", req.getBusCompaniesName())
        		.ifNotBlankLike("departure_city_name", req.getDepartureCityName())
        		.ifNotBlankLike("arrive_city_name", req.getArriveCityName())
        		.orderByDesc("gmt_create"));
    }
}
